import arcpy
import os
import xlrd

# Set paths
input_folder = r"C:\path\to\input\folder"  # Replace with your folder path
output_folder = r"C:\path\to\output\folder"  # Replace with your output folder path

# Loop through all Excel files in the folder
for filename in os.listdir(input_folder):
    if filename.endswith(".xlsx"):
        input_excel = os.path.join(input_folder, filename)  # Full path to Excel file
        output_name = os.path.splitext(filename)[0] + ".shp"  # Shapefile name
        output_shapefile = os.path.join(output_folder, output_name)  # Full path to shapefile

        # Create shapefile
        arcpy.CreateFeatureclass_management(output_folder, output_name, "POINT")

        # Add fields if they don't exist
        existing_fields = [f.name for f in arcpy.ListFields(output_shapefile)]
        fields_to_add = [("station", "DOUBLE"), ("year", "DOUBLE"), ("lon", "DOUBLE"), ("lat", "DOUBLE"), ("MAP", "DOUBLE")]

        for field_name, field_type in fields_to_add:
            if field_name not in existing_fields:
                arcpy.AddField_management(output_shapefile, field_name, field_type)

        # Define coordinate system as GCS_WGS_1984
        arcpy.DefineProjection_management(output_shapefile, "GEOGCS['GCS_WGS_1984',DATUM['D_WGS_1984',SPHEROID['WGS_1984',6378137.0,298.257223563]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]]")

        # Open Excel file
        workbook = xlrd.open_workbook(input_excel)
        sheet = workbook.sheet_by_index(0)

        # Insert cursor
        with arcpy.da.InsertCursor(output_shapefile, ['SHAPE@XY', 'station', 'year', 'lon', 'lat', 'MAP']) as cursor:
            # Loop through Excel rows (skip header row)
            for row in range(1, sheet.nrows):
                try:
                    station = int(sheet.cell_value(row, 0))
                    year = float(sheet.cell_value(row, 1))
                    lon = float(sheet.cell_value(row, 2))
                    lat = float(sheet.cell_value(row, 3))
                    MAP = float(sheet.cell_value(row, 4))

                    # Create point geometry
                    point = arcpy.Point(lon, lat)

                    # Insert row into shapefile
                    cursor.insertRow((point, station, year, lon, lat, MAP))
                except Exception as e:
                    print(f"Error processing row {row}: {e}")

print("Shapefile creation and data insertion completed.")
